<?php
apf_require_class('APF_DB_Factory');
abstract class DAO {

    /**
     * 是否要从master 数据库上读
     * @var unknown_type
     */
    public $read_from_master = true;

    /**
     * memcache key 的前缀
     * @var string
     */
    const MEMCACHE_KEY_PREFIX = "af-core-dao-";

    /**
     * 如果 table 没有 primary key 则使用 缺省的
     * @var unknown_type
     */
    const NONE_TABLE_PK = "nonepk";

    /**
     *
     * 缓存时是否缓存空
     * @var boolean
     */
    public $enable_cache_empty = true;


    public function __construct() {
    }

    /**
     * 返回 当前dao对应的数据库表的名称
     * @return string - table name
     */
    abstract public function get_table_name ();

    /**
     * 返回当前pdo的名称
     * @return string - pdo name
     */
    abstract public function get_read_pdo_name();
    abstract public function get_write_pdo_name();

    /**
     * return the table primary key name
     * @return string - primary key name
     */
    abstract public function get_table_pk ();

    /**
     * 从数据库表中获取$limit行记录
     * @param $where - array eg: array('id'=>$id) = where id = $id;
     * @param $order - string or array eg: id desc or array("id desc","updated desc")
     * @param $limit - 返回的记录数
     * @param $offset - 开始位置
     * @return array - table row
     */
    public function find ($where = array(), $order = null, $limit = 100, $offset = 0) {
        $rt = $this->_fetch_all($where,$order,$limit,$offset);
        return $rt;
    }

    public function find_by_sql ($sql='',$params=array()) {
        $sql = trim($sql);
        if (empty($sql)) {
            return false;
        }
        $rt = $this->execute($sql,$params);
        return $rt;
    }

    /**
     * 从数据库表中获取$limit行记录 $select 得到指定的字段
     * @param $where - array eg: array('id'=>$id) = where id = $id;
     * @param $order - string or array eg: id desc or array("id desc","updated desc")
     * @param $limit - 返回的记录数
     * @param $offset - 开始位置
     * @return array - table row
     */
    public function find_short ($where = array(), $order = null, $limit = 100, $offset = 0, $feild=array()) {
        $rt = $this->_fetch_all_short($where,$order,$limit,$offset,$feild);
        return $rt;
    }

    /**
     * 从数据库表中获取一行记录
     * @see find()
     * @param $where
     * @param $order
     * @return array - table row data
     */
    public function find_row ($where = array(), $order = null) {
        $rt = $this->_fetch_row($where,$order);
        return $rt;
    }

    /**
     * 查找指定条件的行数(包含去重复计数)
     * @param $where - @see find
     * @param $field: 去重字段
     * @return int - table rowsd
     */
    public function find_count ($where = array(), $field = '') {
        $rt = $this->_fetch_count($where, $field);
        return $rt;
    }

    /**
     * 得到指定字段的总合 SUM
     * @param: $where -@see find
     * @return int -feild SUM
     * */
    public function find_sum($feild,$where = array()){
        $data = $this->_fetch_sum($feild,$where);
        return $data;
    }

    /**
     * find table data by primary key
     * @param $id - primary key value
     * @return array
     */
    public function find_by_id ($id) {
        $data = $this->_fetch_by_id($id);
        return $data;
    }


    /**
     * find data by multi primary key value
     * @param $id_array - multi primary key value eg: array($id,$id)
     * @return array
     */
    public function find_by_ids ($id_array) {
        if (empty($id_array) || !is_array($id_array)) {
            return array();
        }
        $_ids = array();
        foreach ($id_array as $id ) {
            $_ids[] = intval($id);
        }
        $_res = $this->_find_by_ids ($_ids);
        return $_res;
    }

    protected  function _find_by_ids ($id_array) {
        if (empty($id_array) || !is_array($id_array)) {
            return array();
        }
        $id_array = $this->format_id_array($id_array);
        $pk = $this->get_table_pk();
        $ids_str = join (',',$id_array);
        $where = "$pk in ($ids_str)";
        $query = $this->_fetch_all($where,null,count($id_array));
        $rs = array();
        foreach ($query as $val) {
            $rs[$val[$pk]] = $val;
        }
        return $rs;
    }

    /**
     * 将pdo返回结果集中的索引改为主键
     * @param array $array
     * @return array
     */
    public function find_assoc($where = array(), $order = null, $limit = 100, $offset = 0) {
        $query = $this->find($where,$order,$limit,$offset);
        if (empty($query)) {
            return array();
        }
        $pk = $this->get_table_pk();
        $rs = array();
        foreach ($query as $val) {
            $rs[$val[$pk]] = $val;
        }
        return $rs;
    }

    /**
     * 将pdo返回结果集中的索引改为主键
     * @param array $array
     * @return array
     */
    public function find_short_assoc($where = array(), $order = null, $limit = 100, $offset = 0,$fields=array()) {
        $query = $this->find_short($where,$order,$limit,$offset,$fields);
        if (empty($query)) {
            return array();
        }
        $pk = $this->get_table_pk();
        $rs = array();
        foreach ($query as $val) {
            $rs[$val[$pk]] = $val;
        }
        return $rs;
    }

    /**
     * 更新数据库表
     * @param $data - array eg: array('id'=>$id)
     * @param $where - array @see
     * @return effect rows
     */
    public function update ($data = array(),$where = array(),$status=FALSE) {
        $rs = $this->_update($data,$where,$status);
        return $rs;
    }


    /**
     * update row by primary key
     * @param $id - primary key value
     * @param $data - update data eg: array('updated'=>$value)
     * @param $status - update status  'field_name = field_name+1'
     * @return effect rows
     */
    public function update_by_id ($id,$data,$status = FALSE ) {
        $rs = $this->_update($data,array($this->get_table_pk()=>$id),$status);
        return $rs;
    }

    /**
     * update rows by multi primary key
     * @param $id_array
     * @param $data
     * @return unknown_type
     */
    public function update_by_ids ($id_array,$data) {
        $rs = $this->_update_by_ids($data,$id_array);
        return $rs;
    }

    /**
     * 向数据库表中插入一行数据
     *
     * @param array $data - array eg:array('column'=>$colvalue)
     * @param array $filter 需要插入DB的列名，有可能传递过来的数据会多于列名
     * @return insert id , if primary key not exists  return effect rows
     */
    public function insert ($data,$filter=array()) {
        $_data = array();
        if (!empty($filter) && is_array($filter)) {
            foreach ($filter as $column) {
                $_data[$column] = $data[$column];
            }
        } else {
            $_data = $data;
        }
        if (empty($_data)) {
            return false;
        }
        $rs = $this->_insert($_data);
        return $rs;
    }

    /**
     * 向数据库批量插入多行数据
     * @param $data - array eg:array(array('column'=>$colvalue1),array('column'=>$colvalue2))
     * warning：每行的列结构应该相同
     */
    public function batch_insert($data, $is_ignore = false){
        if (empty($data)) return false;

        $_data = $data;
        $pdo = $this->get_pdo(true);
        $param = array();
        $values = array();

        //过滤掉重复提交的报错
        $ignore = "";
        if ($is_ignore) {
            $ignore = "ignore";
        }
        $sql = "INSERT {$ignore} INTO ". $this->get_table_name()." (`";
        $sql .= implode('`,`',array_keys(array_pop($data)))."`) VALUES ";
        $param = array();
        foreach($_data as $arr){
            unset($values);
            foreach ($arr as $v) {
                $values[] = "?";
                $param[] = $v;
            }
            $sql .= "(";
            $sql .= implode(",",$values) . "),";
        }
        $sql = rtrim($sql,',');
        $stmt = $pdo->prepare($sql);
        $result = $stmt->execute($param);
        $id = $pdo->lastInsertId();
        if ($id) {
            return $id;
        }
        $count = $stmt->rowCount();
        return ($count == 0) ? $result : $count;
    }

    /**
     * 对数据库表中数据进行删除
     * @param $where - array eg:array('column'=>$colvalue)
     * @return int $result -  effect rows
     */
    public function remove ($where) {
        if (!is_array($where)) {
            return false;
        }
        $pdo = $this->get_pdo(true);
        $_where = $this->build_where($where);
        $sql = "delete from " . $this->get_table_name();
        $sql .= @$_where['where'];
        $stmt = $pdo->prepare($sql);
        $result = $stmt->execute($_where['params']);
        $rs = $stmt->rowCount();
        return ($rs == 0) ? $result : $rs;
    }

    public function remove_by_id ($id) {
        if ($this->get_table_pk() == self::NONE_TABLE_PK) {
            return false;
        }
        if (empty($id)) {
            return false;
        }
        $rs = $this->remove(array($this->get_table_pk()=>$id));
        return $rs;
    }


    public function remove_by_ids ($ids) {
        if ($this->get_table_pk() == self::NONE_TABLE_PK) {
            return false;
        }
        if (empty($ids)) {
            return false;
        }
        if (!is_array($ids)) {
            $ids = array($ids);
        }
        $ids = $this->format_id_array($ids);
        if(empty($ids)) {
            return false;
        }
        $rs = $this->remove(array($this->get_table_pk()." in (".join(',',$ids).") and ?"=>1));
        return $rs;
    }

    final protected function build_key_pk ($id) {
        $table_name = $this->get_table_name();
        $key = self::MEMCACHE_KEY_PREFIX . $table_name . "-".$this->get_table_pk()."_".$id;
        return $key;
    }

    protected function _fetch_by_id ($id) {
        return $this->_fetch_row(array($this->get_table_pk() => $id));
    }

    protected function _fetch_row ($where = array(), $order = null) {
        $rows = (array)$this->_fetch_all($where, $order, 1, 0);
        $rt = array_pop($rows);
        return $rt;
    }

    protected function _fetch_count ($where = array(), $field = '') {
        $pdo = $this->get_pdo ();
        $_where = $this->build_where($where);
        if($field) {
            $sql = "SELECT COUNT(DISTINCT {$field}) AS total_rows FROM " . $this->get_table_name();
        }else {
            $sql = "SELECT COUNT(*) AS total_rows FROM " . $this->get_table_name();
        }
        $sql .= @$_where['where'];
        $stmt = $pdo->prepare($sql);
        $stmt->execute($_where['params']);
        $rs = $stmt->fetch();
        return $rs['total_rows'];
    }

    protected function _fetch_sum ($feild,$where = array()) {
        $pdo = $this->get_pdo ();
        $_where = $this->build_where($where);
        $sql = "SELECT SUM($feild) AS sum FROM " . $this->get_table_name();
        $sql .= @$_where['where'];
        $stmt = $pdo->prepare($sql);
        $stmt->execute($_where['params']);
        $rs = $stmt->fetch();
        return $rs['sum'];
    }

    protected function _fetch_all ($where = array(), $order = null, $limit = 100, $offset = 0) {
        $pdo = $this->get_pdo();
        $sql = "SELECT * FROM " . $this->get_table_name();

        $_where = $this->build_where($where);
        $sql .= @$_where['where'];

        if (is_array($order) && count($order)) {
            $sql .= ' ORDER BY ' . implode(',', $order);
        } else if(is_string($order) && $order) {
            $sql .= ' ORDER BY ' . $order;
        }
        if ($limit > 0) {
            $sql .= " LIMIT " . (int)$limit . " OFFSET " . $offset;
        }
        $stmt = $pdo->prepare($sql);
        $stmt->execute($_where['params']);
        return $stmt->fetchAll();
    }

    protected function _fetch_all_short ($where = array(), $order = null, $limit = 100, $offset = 0, $feild=array()) {
        $pdo = $this->get_pdo();
        $str_select = '*';
        if($feild) {
            $str_select = @implode(',',$feild);
        }
        $sql = "SELECT $str_select FROM " . $this->get_table_name();

        $_where = $this->build_where($where);
        $sql .= @$_where['where'];

        if (is_array($order) && count($order)) {
            $sql .= ' ORDER BY ' . implode(',', $order);
        } else if(is_string($order) && $order) {
            $sql .= ' ORDER BY ' . $order;
        }
        if ($limit > 0) {
            $sql .= " LIMIT " . (int)$limit . " OFFSET " . $offset;
        }
        $stmt = $pdo->prepare($sql);
        $stmt->execute($_where['params']);
        return $stmt->fetchAll();
    }

    protected function build_where ($where) {
        $_where = array('where' => '', 'params' => array());
        if (empty($where)) {
            return $_where;
        }
        if (is_array($where) && count($where)) {
            foreach ($where as $key => $value) {
                if (preg_match("/\?/", $key)) {
                    $_where['field'][] = '(' . $key . ')';
                } else {
                    $_where['field'][] = '(`' . $key . '` = ?)';
                }
                $_where['params'][] = self::escape_value($value);
            }
            $_where['where']=' WHERE ' . implode(' AND ',$_where['field']);
        }else{
            $_where['where']=' WHERE ' . $where;
        }
        return $_where;
    }

    protected function _update ($data = array(),$where = array(),$status = FALSE) {
        $data_count  = count($data);
        if ($data_count < 1) {
            return false;
        }
        if (empty($where)) {
            return false;
        }
        //防止误更新
        if (is_numeric($where)) {
            return false;
        }
        $sql = "UPDATE " . $this->get_table_name() . " SET ";
        $i = 1;
        foreach ($data as $key => $value) {
            $v = self::escape_value($value);
            $sql .= $status ? "`{$key}`= $v ":"`{$key}` = '{$v}'";
            $sql .= ($i < $data_count)?',':'';
            $i ++;
        }
        $_where = $this->build_where($where);
        $sql .= @$_where['where'];
        $pdo = $this->get_pdo(true);
        $stmt = $pdo->prepare($sql);
        $result = $stmt->execute($_where['params']);
        $count = $stmt->rowCount();

        return ($count == 0) ? $result : $count;

    }

    protected function _update_by_ids ($arr,$id_array) {
        if (empty($id_array) || !is_array($id_array)) {
            return 0;
        }
        $id_array = $this->format_id_array($id_array);
        $pdo = $this->get_pdo(true);
        $param = array();
        $sql = "UPDATE " . $this->get_table_name() . " SET ";
        $pk = $this->get_table_pk();
        foreach ($arr as $k => $item) {
            $sql .= "`{$k}`= ?,";
            $param[] = $item;
        }
        $sql = substr($sql, 0, -1);
        $sql .= " WHERE " . $pk . " in (" . implode(",",$id_array) . ")";
        $stmt = $pdo->prepare($sql);
        $result = $stmt->execute($param);
        $rs = $stmt->rowCount();
        return ($rs == 0) ? $result : $rs;
    }

    protected function _insert ($arr) {
        $pdo = $this->get_pdo(true);
        $param = array();
        $values = array();
        foreach ($arr as $v) {
            $values[] = "?";
            $param[] = $v;
        }
        $sql = "INSERT INTO " . $this->get_table_name() . " (`";
        $sql .= implode('`,`',array_keys($arr));
        $sql .= "`) VALUES (";
        $sql .= implode(",",$values) . ")";
        $stmt = $pdo->prepare($sql);
        $result = $stmt->execute($param);
        $id = $pdo->lastInsertId();
        if ($id) {
            return $id;
        }
        $count = $stmt->rowCount();

        return ($count == 0) ? $result : $count;
    }

    /**
     * @var APF_DB_Factory
     */
    private $_dbfactory = null;

    /**
     * 获取一个 pdo 实例
     * @param $write boolean - true :master db connection false: slave db connection
     * @return APF_DB_PDO
     */
    protected function get_pdo ($write = false) {
        if (null === $this->_dbfactory) {
            $this->_dbfactory = APF_DB_Factory::get_instance();
        }
        if ($write) {
            return $this->_dbfactory->get_pdo($this->get_write_pdo_name());
        } else {
            if ($this->read_from_master) {
                return $this->_dbfactory->get_pdo($this->get_write_pdo_name());
            }
        }
        return $this->_dbfactory->get_pdo($this->get_read_pdo_name());
    }

    public static function format_to_string ($data) {
        if (is_array($data)) {
            $str = "";
            foreach ($data as $key => $value) {
                $str .= $key . "," . $value;
            }
            return $str;
        }
        return $data;
    }


    /**
     * 执行SQL语句
     *
     * @param string $sql SQL语句
     * @param array $params SQL绑定参数
     * @param boolean $write 数据库主机选择
     * @return array
     */
    public function execute($sql, $params = array(), $write = false) {
        $pdo = $this->get_pdo($write);
        $stmt = $pdo->prepare($sql);
        if (!$stmt->execute((array)$params)) {
            return false;
        }

        $type = strtoupper(substr($sql, 0, 6));
        $result = null;
        switch ($type) {
            case 'INSERT':
                $result = $pdo->lastInsertId();
                if (!$result) {
                    $result = $stmt->rowCount();
                }
                break;

            case 'UPDATE':
            case 'DELETE':
                $result = $stmt->rowCount();
                break;

            case 'SELECT':
                $result = $stmt->fetchAll();
                break;

            default:
                break;
        }

        return $result;
    }

    /**
     * 格式化ID数组，确保数组中全部是数字
     * @param $id_array
     */
    public function format_id_array($id_array) {
        foreach ($id_array as $key=>$val) {
            $val = intval($val);
            if ($val<1) {
                unset($id_array[$key]);
            }else {
                $id_array[$key] = $val;
            }
        }
        return $id_array;
    }

    public static function escape_like ($value) {
        if (is_numeric($value) || is_string($value)) {
            $value = self::escape_value($value);
            return str_replace(array('_','%'),array('\_','\%'),$value);
        } else {
            return $value;
        }
    }

    public static function escape_value ($value) {
        if (is_numeric($value) || is_string($value)) {
            return str_replace(array("'",'\\'),array("''",'\\\\'),$value);
        } else {
            return $value;
        }
    }

    //显示测试内容
    function d($var, $die = TRUE)
    {
        switch (TRUE) {
            case is_bool($var):
                $var = $var ? 'TRUE' : 'FALSE';
                break;
            case is_null($var):
                $var = 'NULL';
                break;
        }
        echo '<pre>';
        echo print_r($var, TRUE);
        echo '</pre>';
        if ($die === TRUE) {
            die();
        }
    }

}